from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        CREATE TABLE IF NOT EXISTS "logs" (
    "id" BIGSERIAL NOT NULL PRIMARY KEY,
    "request_id" VARCHAR(255) NOT NULL UNIQUE,
    "created_at" TIMESTAMPTZ NOT NULL UNIQUE DEFAULT CURRENT_TIMESTAMP,
    "processing_time_ms" INT NOT NULL,
    "client_ip" VARCHAR(100),
    "user_no" VARCHAR(100),
    "user_name" VARCHAR(100),
    "path" VARCHAR(255) NOT NULL,
    "method" VARCHAR(10) NOT NULL,
    "request_headers" JSONB,
    "query_params" JSONB,
    "request_body" JSONB,
    "status_code" INT NOT NULL,
    "response_body" JSONB
);
COMMENT ON COLUMN "logs"."id" IS '日志ID';
COMMENT ON COLUMN "logs"."request_id" IS '请求唯一ID';
COMMENT ON COLUMN "logs"."created_at" IS '请求时间';
COMMENT ON COLUMN "logs"."processing_time_ms" IS '耗时（毫秒）';
COMMENT ON COLUMN "logs"."client_ip" IS '客户端IP';
COMMENT ON COLUMN "logs"."user_no" IS '用户编号';
COMMENT ON COLUMN "logs"."user_name" IS '用户名称';
COMMENT ON COLUMN "logs"."path" IS '请求路径';
COMMENT ON COLUMN "logs"."method" IS '请求方法';
COMMENT ON COLUMN "logs"."request_headers" IS '请求头';
COMMENT ON COLUMN "logs"."query_params" IS '查询参数';
COMMENT ON COLUMN "logs"."request_body" IS '请求体';
COMMENT ON COLUMN "logs"."status_code" IS '响应状态码';
COMMENT ON COLUMN "logs"."response_body" IS '响应体';
COMMENT ON TABLE "logs" IS 'API 请求日志表';
CREATE TABLE IF NOT EXISTS "role" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "create_time" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "create_by" VARCHAR(32) NOT NULL DEFAULT '',
    "update_time" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "update_by" VARCHAR(32) NOT NULL DEFAULT '',
    "role_no" VARCHAR(20) NOT NULL UNIQUE,
    "role_name" VARCHAR(50) NOT NULL UNIQUE,
    "role_desc" VARCHAR(200) NOT NULL,
    "status" BOOL NOT NULL DEFAULT True
);
CREATE INDEX IF NOT EXISTS "idx_role_role_no_59a7e7" ON "role" ("role_no");
COMMENT ON COLUMN "role"."create_time" IS '创建时间';
COMMENT ON COLUMN "role"."create_by" IS '创建人';
COMMENT ON COLUMN "role"."update_time" IS '更新时间';
COMMENT ON COLUMN "role"."update_by" IS '更新人';
COMMENT ON COLUMN "role"."role_no" IS '角色编号';
COMMENT ON COLUMN "role"."role_name" IS '角色名称';
COMMENT ON COLUMN "role"."role_desc" IS '角色描述';
COMMENT ON COLUMN "role"."status" IS '状态';
COMMENT ON TABLE "role" IS '系统用户表';
CREATE TABLE IF NOT EXISTS "user" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "create_time" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "create_by" VARCHAR(32) NOT NULL DEFAULT '',
    "update_time" TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    "update_by" VARCHAR(32) NOT NULL DEFAULT '',
    "user_no" VARCHAR(20) NOT NULL UNIQUE,
    "password" VARCHAR(255) NOT NULL,
    "user_name" VARCHAR(20) NOT NULL,
    "nick_name" VARCHAR(20) NOT NULL,
    "user_gender" VARCHAR(1) NOT NULL,
    "user_email" VARCHAR(20) NOT NULL,
    "user_phone" VARCHAR(11) NOT NULL,
    "role_list" JSONB NOT NULL,
    "status" BOOL NOT NULL DEFAULT True,
    "last_login" TIMESTAMPTZ,
    CONSTRAINT "uid_user_user_na_08c35c" UNIQUE ("user_name", "nick_name")
);
CREATE INDEX IF NOT EXISTS "idx_user_user_no_4dfb5f" ON "user" ("user_no");
COMMENT ON COLUMN "user"."create_time" IS '创建时间';
COMMENT ON COLUMN "user"."create_by" IS '创建人';
COMMENT ON COLUMN "user"."update_time" IS '更新时间';
COMMENT ON COLUMN "user"."update_by" IS '更新人';
COMMENT ON COLUMN "user"."user_no" IS '登录编号';
COMMENT ON COLUMN "user"."password" IS '登录密码';
COMMENT ON COLUMN "user"."user_name" IS '姓名';
COMMENT ON COLUMN "user"."nick_name" IS '昵称';
COMMENT ON COLUMN "user"."user_gender" IS '性别';
COMMENT ON COLUMN "user"."user_email" IS '邮箱';
COMMENT ON COLUMN "user"."user_phone" IS '手机';
COMMENT ON COLUMN "user"."role_list" IS '角色列表';
COMMENT ON COLUMN "user"."status" IS '状态';
COMMENT ON COLUMN "user"."last_login" IS '用户最后登录时间';
COMMENT ON TABLE "user" IS '系统用户表';
CREATE TABLE IF NOT EXISTS "aerich" (
    "id" SERIAL NOT NULL PRIMARY KEY,
    "version" VARCHAR(255) NOT NULL,
    "app" VARCHAR(100) NOT NULL,
    "content" JSONB NOT NULL
);"""


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        """
